# _*_ coding:utf-8 _*_
# @File  : user_chart.py
# @Time  : 2020-09-08 9:08
# @Author: zizle

""" 图形相关
API-1: 保存一个图形的配置
API-2: 一张表内的所有图形(模板渲染)
API-3: 单个图形的配置和作图数据
API-4: 单个品种的所有图形列表(模板渲染/JSON)
API-5: 获取单个图形的基本信息(含基本配置信息)
API-6: 修改单个图形的解读描述
API-7: 交换两个图形的排序后缀suffix
API-8: 修改图形主页显示与品种也显示与否
API-9: 删除图形及相关配置文件
"""
import re
import os
import json
import jsonpath
import numpy as np
import pandas as pd
from datetime import datetime, timedelta
from fastapi import APIRouter, Depends, HTTPException, Body, Query, Path
from fastapi.templating import Jinja2Templates
from fastapi.requests import Request
from utils.encryptor import generate_chart_option_filepath
from db import FAConnection
from db.mysql_z import MySqlZ, VarietySheetDB
from configs import FILE_STORAGE
from logger import logger
from .models import ChartOption, SwapSuffixItem, ModifyChartOptionItem
from .compare_util import week_compare, month_compare, year_compare
from hutool import security
from interfaces.depends import logged_require
from v1_all_api.all_response import AllResponse
from v1_all_api.all_utils import datetime_utils, datalib_utils

chart_router = APIRouter()

# 挂载模板文件夹
template = Jinja2Templates(directory='templates')


# 验证品种
def verify_variety(variety_en: str):
    if not re.match(r'^[A-Z]{1,2}$', variety_en):
        raise HTTPException(detail="Invalidate Variety!", status_code=400)
    return variety_en


@chart_router.post("/sheet/{sheet_id}/chart/", summary="为一张表保存图形配置")
async def sheet_chart(sheet_id: int, person: dict = Depends(logged_require),
                      chart_option: ChartOption = Body(...)):
    # user_id, _ = decipher_user_token(user_token)
    # if not user_id:
    #     raise HTTPException(status_code=401, detail="Token Expired!")
    user_id = person['uid']
    if not all([chart_option.title, chart_option.variety_en, chart_option.option]):
        raise HTTPException(status_code=400, detail="Option Error!")
    relative_path = "ChartOption/{}".format(generate_chart_option_filepath(user_id))
    option_file = os.path.join(FILE_STORAGE, relative_path)
    # 创建文件夹
    file_folder = os.path.split(option_file)[0]
    if not os.path.exists(file_folder):
        os.makedirs(file_folder)
    with open(option_file, 'w', encoding='utf-8') as fp:
        json.dump(chart_option.option, fp, indent=4)
    # 保存到数据库
    category = chart_option.option['chart_category']
    save_dict = {
        "creator": user_id,
        "title": chart_option.title,
        "variety_en": chart_option.variety_en,
        "sheet_id": sheet_id,
        "option_file": relative_path,
        "decipherment": chart_option.decipherment,
        "is_private": 1 if chart_option.is_private else 0,
        "group_name": category
    }
    try:
        with MySqlZ() as cursor:
            cursor.execute(
                "INSERT INTO industry_user_chart "
                "(creator,title,variety_en,sheet_id,option_file,group_name,decipherment,is_private) "
                "VALUES (%(creator)s,%(title)s,%(variety_en)s,%(sheet_id)s,%(option_file)s,"
                "%(group_name)s,%(decipherment)s,%(is_private)s);",
                save_dict
            )
            # 更新后缀
            new_id = cursor._instance.insert_id()
            cursor.execute("UPDATE industry_user_chart SET suffix=id WHERE id=%s;", (new_id,))
    except Exception as e:
        # 保存失败删除已保存的json文件
        if os.path.exists(option_file):
            os.remove(option_file)
        logger.error("用户保存图形配置失败:{}".format(e))
        return {"message": "保存图形失败"}
    else:
        return {"message": "保存图形配置成功!"}


@chart_router.get("/sheet/{sheet_id}/chart/", summary="获取一张表的所有图形信息")
async def get_sheet_charts(request: Request, sheet_id: int, is_own: int = Query(0, ge=0, le=1),
                           token: str = Query(...)):
    person = security.decrypt_access_token(token)
    if not person:
        raise HTTPException(status_code=401, detail='Un Authorization!')
    user_id = person['uid']
    # 查询这个表格所有图形信息
    with MySqlZ() as cursor:
        cursor.execute(
            "SELECT id, DATE_FORMAT(create_time,'%%Y-%%m-%%d') AS create_time,creator,"
            "title,variety_en,sheet_id,option_file,decipherment,suffix,is_principal,is_petit "
            "FROM industry_user_chart "
            "WHERE IF(%s=creator,TRUE,is_private=0) AND IF(%s=0,TRUE,creator=%s) AND sheet_id=%s "
            "ORDER BY suffix ASC;",
            (user_id, is_own, user_id, sheet_id,)
        )
        charts = cursor.fetchall()
    render_file = 'sheet_charts_v2.html' if person['role'] == 'normal' else 'sheet_charts_inv2.html'
    return template.TemplateResponse(
        render_file,
        {
            "request": request,
            "has_chart": len(charts),
            "sheet_charts": charts
        }
    )


""" 返回图形配置和数据开始 """


def generate_days_of_year():
    """ 生成一年的每一月每一天 """
    days_list = list()
    start_day = datetime.strptime("2020-01-01", "%Y-%m-%d")
    end_day = datetime.strptime("2020-12-31", "%Y-%m-%d")
    while start_day <= end_day:
        days_list.append(start_day.strftime("%m-%d"))
        start_day += timedelta(days=1)
    return days_list


def get_season_chart_source(source_df):
    """ 处理季节图形的数据 """
    target_values = dict()  # 保存最终数据的字典
    # 获取column_0的最大值和最小值
    min_date = source_df["column_0"].min()
    max_date = source_df["column_0"].max()
    start_year = int(min_date[:4])
    end_year = int(max_date[:4])
    min_labelx, max_labelx = '12-31', '01-01'
    for year in range(start_year, end_year + 1):
        # 获取当年的第一天和最后一天
        current_first = str(year) + "-01-01"
        current_last = str(year) + "-12-31"
        # 从data frame中取本年度的数据并转为列表字典格式
        current_year_df = source_df[(source_df["column_0"] >= current_first) & (source_df["column_0"] <= current_last)]
        current_year_df["column_0"] = current_year_df["column_0"].apply(lambda x: x[5:])
        cur_xmin, cur_xmax = current_year_df['column_0'].min(), current_year_df['column_0'].max()
        if cur_xmin < min_labelx:
            min_labelx = cur_xmin
        if cur_xmax > max_labelx:
            max_labelx = cur_xmax

        target_values[str(year)] = current_year_df.to_dict(orient="record")

    target_values["xAxisData"] = [d for d in generate_days_of_year() if min_labelx <= d <= max_labelx]
    return target_values


def get_interval_chart_source(source_df, analysis_column):
    def format_value_float(x):
        try:
            return float(x)
        except Exception:
            return np.NAN
    # 取出分析的数据
    source_df = source_df[['column_0', analysis_column]]
    source_df.columns = ['date_str', 'value']
    # 删除value为'-'的数据
    # source_df['value'] = source_df['value'].apply(str)
    # source_df = source_df[source_df['value'] != '-']
    source_df['value'] = source_df['value'].apply(lambda x: format_value_float(x))

    # 数据处理,得到区间的最值和当前年度值等数据
    source_df['year'] = source_df['date_str'].apply(lambda x: x[:4])
    source_df['month_date'] = source_df['date_str'].apply(lambda x: x[5:])
    # 一年内所有的日期
    all_date = [[item.strftime('%m-%d')] for item in
                pd.date_range('2016-01-01', '2016-12-31').tolist()]  # 选择2016其为闰年
    date_df = pd.DataFrame(all_date, columns=['month_date'])  # 一年内所有的日期
    cur_year, cur_date = datetime.today().year, datetime.today().strftime('%m-%d')
    to_culdf = source_df[source_df['year'] <= str(cur_year)].copy()  # 计算的数据(年份小于当前年份的)
    current_df = source_df[source_df['year'] == str(cur_year)].copy()  # 当前年度的数据
    del source_df

    # 以年分组,循环每个dataframe,处理后横向拼接
    temp_df = pd.DataFrame()
    for year in to_culdf.groupby(by=['year'], as_index=False).groups:
        year_df = to_culdf[to_culdf['year'] == year]
        year_df = pd.merge(year_df, date_df, on='month_date', how='outer')  # 横向拼接
        year_df.sort_values(by=['month_date'], inplace=True)  # 排序
        year_df.fillna(method='ffill', inplace=True)  # 使用上值填充NAN
        year_df.fillna(method='bfill', inplace=True)  # 使用下方第一个非NAN填充(补充开年几天的数值)
        if year == str(cur_year):  # 当前年
            year_df = year_df[year_df['month_date'] <= cur_date]  # 当前年取到当前日期
        temp_df = pd.concat([temp_df, year_df])  # 竖向拼接
    temp_df.sort_values(by=['month_date', 'year'], inplace=True)  # 排序

    min_df = temp_df.groupby(by=['month_date'], as_index=False)['value'].min()
    max_df = temp_df.groupby(by=['month_date'], as_index=False)['value'].max()
    mean_df = temp_df.groupby(by=['month_date'], as_index=False)['value'].mean()

    # 横向拼接
    ret_df = pd.merge(min_df, max_df, on='month_date')
    ret_df.columns = ['month_date', 'min', 'max']
    ret_df = pd.merge(ret_df, mean_df, on='month_date')
    ret_df.columns = ['month_date', 'min', 'max', 'mean']

    ret_df = pd.merge(ret_df, current_df[['month_date', 'value']], on='month_date', how='left')  # 拼入当前年度数据
    ret_df['mean'] = ret_df['mean'].apply(lambda x: round(x, 2))
    ret_df.fillna(method='ffill', inplace=True)
    ret_df['value'] = ret_df['value'].mask(ret_df['month_date'] > cur_date, '-')  # 当前年度大于今日的数据处理
    ret_df.drop_duplicates(subset=['min', 'max', 'mean', 'value'], keep='last', inplace=True)
    ret_df.fillna('-', inplace=True)
    return ret_df.to_dict(orient='records')


def replace_zero_to_middle_line(data_str):
    """ 替换数据中的0为中横线 """
    try:
        value = float(data_str)  # 转为float,可检测0或0.00或0.000等字符串
    except Exception:
        return '-'
    else:
        return '-' if value == 0 else data_str


def sheet_data_handler(base_option, source_dataframe, is_dated):
    """ 根据图形配置处理表格的数据 """
    chart_type = base_option["chart_category"]
    # 取数据的表头(表头名称用于作图时的图例)
    headers_df = source_dataframe.iloc[:1]
    # 将表头转为字典
    headers_dict = headers_df.to_dict(orient="record")[0]
    values_df = source_dataframe.iloc[2:]
    # 取最大值和最小值
    start_year = base_option["start_year"]
    end_year = base_option["end_year"]
    if start_year > "0":
        start_date = str(start_year) + "-01-01"
        # 切出大于开始日期的数据
        values_df = values_df[values_df["column_0"] >= start_date]
    if end_year > "0":
        # 切出小于结束日期的数据
        end_date = str(end_year) + "-12-31"  # 含结束年份end_year + 1
        values_df = values_df[values_df["column_0"] <= end_date]
    # 数据是否去0处理
    for series_item in base_option["series_data"]:
        column_index = series_item["column_index"]
        contain_zero = series_item["contain_zero"]
        if not contain_zero:  # 数据不含0,去0处理
            values_df[column_index] = values_df[column_index].apply(replace_zero_to_middle_line)
            # values_df = values_df[values_df[column_index] != "0"]
    # 日期序列进行排序
    if is_dated:
        values_df = values_df.sort_values(by="column_0")  # 最后进行数据从小到大的时间排序
    # table_show_df.reset_index(inplace=True)  # 重置索引,让排序生效(赋予row正确的值。可不操作,转为json后,索引无用处了)
    #
    # 普通图形返回结果数据
    if chart_type == "normal":
        # 日期序列处理横轴的格式
        if is_dated:
            date_length = base_option["x_axis"]["date_length"]
            values_df["column_0"] = values_df["column_0"].apply(lambda x: x[:date_length])
        values_json = values_df.to_dict(orient="record")
    elif chart_type == "season":  # 季节图形将数据分为{year1: values1, year2: values2}型
        # 裁掉column_index数据 == '-'的
        column_index = base_option.get('series_data')[0]["column_index"]
        values_df = values_df[values_df[column_index] != '-']

        values_json = get_season_chart_source(values_df.copy())
    elif chart_type == 'interval':  # 区间带图形
        analysis_column = base_option['series_data'][0]['column_index']
        values_json = get_interval_chart_source(values_df.copy(), analysis_column)
    else:
        values_json = []
    return values_json, headers_dict.copy()


@chart_router.get("/chart-option/{chart_id}/", summary="获取某个图形的配置和数据")
async def chart_option_values(chart_id: int):
    # 查询出表格和配置
    with MySqlZ() as cursor:
        cursor.execute(
            "SELECT charttb.id,charttb.title,charttb.option_file,sheettb.db_table,sheettb.is_dated "
            "FROM industry_user_chart AS charttb "
            "INNER JOIN industry_user_sheet AS sheettb "
            "ON charttb.sheet_id=sheettb.id AND charttb.id=%s;",
            (chart_id,)
        )
        chart_info = cursor.fetchone()
    if not chart_info:
        return {}
    # 获取配置
    option_file = os.path.join(FILE_STORAGE, chart_info["option_file"])
    is_dated = chart_info['is_dated']
    if not os.path.exists(option_file):
        return {}
    with open(option_file, 'r') as fp:
        base_option = json.load(fp)
    # 查询出表的具体数据
    sheet_table = chart_info["db_table"]
    with VarietySheetDB() as cursor:
        cursor.execute("SELECT * FROM %s;" % sheet_table)
        sheet_data = cursor.fetchall()
    # 处理数据
    chart_values, headers_dict = sheet_data_handler(base_option, pd.DataFrame(sheet_data), is_dated)
    return {"message": "获取数据成功!", "chart_type": base_option["chart_category"], "base_option": base_option,
            "chart_values": chart_values, "sheet_headers": headers_dict}


""" 返回图形配置和数据结束 """


@chart_router.get('/chart-compare/{chart_id}/', summary='获取图形的对比解读描述')
async def chart_compares_description(chart_id: int = Path(..., ge=1)):
    # 通过chart_id获取option
    with MySqlZ() as cursor:
        cursor.execute('SELECT id,option_file,sheet_id FROM industry_user_chart WHERE id=%s;', (chart_id,))
        cur_obj = cursor.fetchone()
        option_file = cur_obj['option_file']
        sheet_id = cur_obj['sheet_id']
        # print('sheet_id', sheet_id)
        # 查询db_name
        cursor.execute('SELECT id,db_table,is_dated FROM industry_user_sheet WHERE id=%s;', (sheet_id, ))
        sheet_obj = cursor.fetchone()
        db_name = sheet_obj.get('db_table', None)
        is_dated = sheet_obj.get('is_dated', None)
    if not db_name or not is_dated:
        return {'message': '查询成功,没查询到对应数据表或非日期序列数据无法对比!', 'compare_text': ''}
    filepath = os.path.join(FILE_STORAGE, option_file)
    with open(filepath, 'r') as fp:
        option = json.load(fp)
    # 读取对比列，进行数据对比
    compare = option.get('compares', {})
    if not compare:
        return {'message': '查询成功,未设置对比项!', 'compare_text': ''}
    # 查询表数据
    # print('db_name', db_name)
    with VarietySheetDB() as v_cursor:
        # 查id=1的headers
        v_cursor.execute(f'SELECT * FROM {db_name} WHERE id=1;')
        col_headers = v_cursor.fetchone()
        # 查询至少近一年数据
        v_cursor.execute(f'SELECT * FROM {db_name} WHERE id>2 ORDER BY column_0 DESC LIMIT 360;')
        sheet_data = v_cursor.fetchall()
    df = pd.DataFrame(sheet_data)
    # print(df)
    # print(compare)
    # print(col_headers)
    # 分析数据
    week_text = ''
    month_text = ''
    year_text = ''
    for key, com_values in compare.items():
        column_df = df[['column_0', key]].copy()  # 根据key取数小df
        if 'week' in com_values:
            week_text += week_compare(column_df.copy(), col_headers[key])
        if 'month' in com_values:
            month_text += month_compare(column_df.copy(), col_headers[key])
        if 'year' in com_values:
            year_text += year_compare(column_df.copy(), col_headers[key])

        del column_df
    if not week_text and not month_text and not year_text:
        return {'message': '查询成功', 'compare_text': ''}
    # 生成文字返回
    return {'message': '查询成功', 'compare_text': f'对比解读：{week_text}\n{month_text}\n{year_text}'}


@chart_router.get("/industry/chart/", summary="渲染主页的图形")
async def industry_chart(request: Request):
    with MySqlZ() as cursor:
        cursor.execute(
            "SELECT id, DATE_FORMAT(create_time,'%%Y-%%m-%%d') AS create_time,creator,"
            "title,variety_en,sheet_id,option_file,decipherment,suffix,is_principal,is_petit "
            "FROM industry_user_chart WHERE is_principal='2' AND is_private=0;"
        )
        charts = cursor.fetchall()
    return template.TemplateResponse(
        "sheet_charts_v2.html",
        {
            "request": request,
            "has_chart": len(charts),
            "sheet_charts": charts
        }
    )


# 20220824写一个新版本的图形渲染接口(给前端客户端使用)
@chart_router.get('/variety/chartRender/')
def datalib_variety_chart(request: Request, token: str = Query(...), variety: str = Query(...)):
    person = security.decrypt_access_token(token)
    if not person:
        raise HTTPException(status_code=401, detail='Un Authorization!')
    # 查询品种下的图形列表
    sql = """
            SELECT id,chart_type,name,user_id,is_open,watermark,sorted,startdate,enddate,xaxis_format 
            FROM datalib_variety_chart WHERE variety=%s AND is_open=1 
            ORDER BY sorted,id;
        """
    db_conn = FAConnection()
    records = db_conn.query(sql, [variety])
    return template.TemplateResponse(
        'datalib_sheet_charts.html',
        {
            "request": request,
            "has_chart": len(records),
            "sheet_charts": records
        }
    )


# 20220824写一个新版本的表下图形渲染接口(给前端客户端使用)
@chart_router.get('/sheetChart/render/')
def sheet_chart_render(request: Request, token: str = Query(...), sheet: int = Query(...)):
    person = security.decrypt_access_token(token)
    if not person:
        raise HTTPException(status_code=401, detail='Un Authorization!')
    # 查询品种下的图形列表
    sql = """
        SELECT id,chart_type,name,user_id,is_open,watermark,sorted,startdate,enddate,xaxis_format 
        FROM datalib_variety_chart WHERE sheet_id=%s AND is_open=1 
        ORDER BY sorted,id;
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, [sheet])
    return template.TemplateResponse(
        'datalib_sheet_charts.html',
        {
            "request": request,
            "has_chart": len(records),
            "sheet_charts": records
        }
    )


# 从v1接口(datalib/variety.py)复制过来的，支持http访问(给前端客户端使用)
@chart_router.get('/chartOption/')  # 根据ID获取图形具体配置数据
async def get_chart_option(cid: int = Query(..., ge=1)):
    query_sql = 'SELECT id,chart_type,name,startdate,enddate,xaxis_format,options,indication ' \
                'FROM datalib_variety_chart WHERE id=%s;'
    db_conn = FAConnection()
    chart_obj = db_conn.query(query_sql, [cid], fetchone=True)[0]
    if not chart_obj:
        return AllResponse.server_no_content()
    # 查询数据，并处理出图形echarts的配置里需要的data
    indication = json.loads(chart_obj['indication'])  # 需要的指标
    chart_option = json.loads(chart_obj['options'])  # 图形配置空壳

    df, headers = await datalib_utils.get_index_formula_dataframe(column_templates=indication, na_fill=np.NAN,
                                                                  start_date=chart_obj['startdate'],
                                                                  end_date=chart_obj['enddate'])
    df['datadate'] = df['datadate'].apply(lambda x: x.strftime(chart_obj['xaxis_format']))

    # 环比数据计算
    link_ratios = []
    for col in headers:
        if not col['isChecked']:
            continue
        lr_df = df.tail(5)
        lr_df['link_ratio'] = lr_df[col['prop']].pct_change().round(4)
        lr_df = lr_df.fillna('')
        link_ratios.append({
            'name': col['name'],
            'unit': col['unit'],
            'datadate': lr_df.iloc[-1]['datadate'],
            'datavalue': lr_df.iloc[-1][col['prop']],
            'link_ratio': lr_df.iloc[-1]['link_ratio'],
        })

    df.fillna('', inplace=True)  # 填充NAN数据
    # ------- 源数据处理完毕
    # 填充option中的data数据
    # 1. xAxisData 2. series 3. legendData
    if chart_obj['chart_type'] == 'season':  # 季节图形
        # 取出作图列的prop
        prop_name = ''
        for i_item in indication:
            if i_item['isChecked']:
                prop_name = i_item['prop']
                break
        x_axis_data = datetime_utils.date_of_year()
        # 数据源按年分组
        df['year'] = df['datadate'].apply(lambda x: x[:4])
        df['date'] = df['datadate'].apply(lambda x: x[5:])
        series = []
        legend_data = []
        for year_group in df.groupby(by='year').groups:
            y_df = pd.merge(pd.DataFrame(data=x_axis_data, columns=['date']), df[df['year'] == year_group], on='date',
                            how='left')
            # y_df.fillna(None, inplace=True)
            series.append({
                'symbol': 'none', 'connectNulls': True, 'name': year_group,
                'type': 'line', 'yAxisIndex': 0,
                'data': list(
                    map(lambda x: None if pd.isna(x[prop_name]) else x[prop_name], y_df.to_dict(orient='records')))
            })
            legend_data.append({'name': year_group})
    else:  # 普通图形
        chart_source_data = df.to_dict(orient='records')
        x_axis_data = list(map(lambda x: x['datadate'], chart_source_data))
        series = []
        legend_data = []
        for i_item in indication:
            if i_item['isChecked']:
                o_series = {'symbol': 'none', 'connectNulls': True, 'name': i_item['name'],
                            'type': i_item['chartType'], 'yAxisIndex': i_item['yIndex'],
                            'data': list(map(lambda x: x[i_item['prop']], chart_source_data))}
                series.append(o_series)
                legend_data.append({'name': i_item['name']})
    # 放入具体的配置中
    chart_option['xAxis'][0]['data'] = x_axis_data
    chart_option['series'] = series
    chart_option['legend']['data'] = legend_data
    # 十字光标 原来有tooltip={trigger:axis}
    chart_option['tooltip']['axisPointer'] = {'type': 'cross'}
    # 颜色
    chart_option['color'] = ['#5b9bd5', '#ed7d31', '#ffc000', '#4472c4', '#70ad47', '#255e91',
                             '#9e480e', '#636363', '#997300', '#264478', '#43682b', '#7cafdd',
                             '#f1975a', '#ffcd33', '#698ed0', '#8cc168', '#327dc2', '#d26012']
    # 标题居中
    chart_option['title']['left'] = 'center'
    # 放入当前请求图形的id
    chart_option['chart_id'] = cid
    response_data = {
        'chart_option': chart_option,
        'link_ratios': link_ratios
    }
    return AllResponse.operate_successfully(data=response_data)


@chart_router.get("/variety/{variety_en}/chart/", summary="获取品种的所有图形列表")
async def variety_chart(
        request: Request,
        variety_en: str = Depends(verify_variety),
        is_own: int = Query(0, ge=0, le=1),
        render: int = Query(0, ge=0, le=1),
        is_petit: int = Query(0, ge=0, le=1),
        category: str = Query('0'),
        token: str = Query(...),
        v: str = Query('v2')
):
    person = security.decrypt_access_token(token)
    if not person:
        raise HTTPException(status_code=401, detail='Un Authorization!')
    user_id = person['uid']
    with MySqlZ() as cursor:
        cursor.execute(
            "SELECT id, DATE_FORMAT(create_time,'%%Y-%%m-%%d') AS create_time,creator,"
            "title,variety_en,sheet_id,option_file,group_name,decipherment,suffix,is_principal,is_petit,is_private "
            "FROM industry_user_chart "
            "WHERE variety_en=%s AND IF(%s=0,TRUE,creator=%s) AND IF(%s=creator,TRUE,is_private=0) AND IF(%s=0,TRUE,is_petit=1) "
            "ORDER BY suffix ASC;",
            (variety_en, is_own, user_id, user_id, is_petit)
        )
        charts = cursor.fetchall()
        cursor.execute("SELECT id,username FROM user_user WHERE role<>'normal';")
        user_list = cursor.fetchall()
        user_dict = {user_item["id"]: user_item["username"] for user_item in user_list}
    if category != '0':
        # 只返回指定类型的图形
        charts = list(filter(lambda x: x['group_name'] == category, charts))
    for chart_item in charts:
        chart_item["creator"] = user_dict.get(chart_item["creator"])
    if render:  # 模板渲染
        # template_filename = 'sheet_charts_v2.html' if v == 'v2' else 'sheet_charts.html'  # 统一使用v2渲染
        render_file = 'sheet_charts_v2.html' if person['role'] == 'normal' else 'sheet_charts_inv2.html'
        return template.TemplateResponse(
            render_file,
            {
                "request": request,
                "has_chart": len(charts),
                "sheet_charts": charts
            }
        )
    else:
        return {"message": "获取图形信息成功!", "data": charts}


# 本API必须置于.put('/chart/{chart_id}/')之前,否则`suffix-swap`先被匹配为错误的chart_id而无法执行
@chart_router.put("/chart/suffix-swap/", summary="交换图形排序后缀")
async def swap_chart_suffix(swap_item: SwapSuffixItem = Body(...)):
    with MySqlZ() as cursor:
        # 交换
        cursor.execute(
            "UPDATE industry_user_chart AS sheet1 "
            "JOIN industry_user_chart AS sheet2 "
            "ON sheet1.id=%s AND sheet2.id=%s "
            "SET sheet1.suffix=sheet2.suffix,sheet2.suffix=sheet1.suffix;",
            (swap_item.swap_id, swap_item.to_swap)
        )
    return {"message": "交换排序成功!", "swap_row": swap_item.swap_row}


@chart_router.put("/chart/suffix/", summary='设置图形顺序')
async def set_chart_suffix(move_id: int = Body(...), to_id: int = Body(...)):
    with MySqlZ() as cursor:
        # 查询
        cursor.execute(
            "SELECT sheet1.id as id1, sheet1.suffix as suffix1,"
            "sheet2.id as id2, sheet2.suffix as suffix2 "
            "FROM industry_user_chart AS sheet1 "
            "JOIN industry_user_chart AS sheet2 "
            "ON sheet1.id=%s AND sheet2.id=%s;", (move_id, to_id)
        )
        relt = cursor.fetchall()[0]
        if relt['suffix1'] == relt['suffix2']:
            relt['suffix1'] += 1
        else:
            relt['suffix1'] = relt['suffix2'] + 1
        cursor.execute(
            "UPDATE industry_user_chart SET suffix=%s WHERE id=%s LIMIT 1;",
            (relt['suffix1'], relt['id1'])
        )
        # 交换
        # cursor.execute(
        #     "UPDATE industry_user_chart AS sheet1 "
        #     "JOIN industry_user_chart AS sheet2 "
        #     "ON sheet1.id=%s AND sheet2.id=%s "
        #     "SET sheet1.suffix=sheet2.suffix);",
        #     (move_id, to_id)
        # )
    return {"message": "设置成功!"}


@chart_router.get('/chart/sheet-headers/', summary='图形所对应表的列名称')
async def get_chart_sheet_headers(sid: int = Query(..., ge=1), cid: int = Query(..., ge=1)):
    # sid为sheet的id,查询sid的表头列
    with MySqlZ() as cursor:
        cursor.execute(
            'SELECT id,db_table FROM industry_user_sheet WHERE id=%s;', (sid,)
        )
        table_name = cursor.fetchone()['db_table']
        # 查询chart_option
        cursor.execute('SELECT id,option_file FROM industry_user_chart WHERE id=%s;', (cid,))
        option_file = cursor.fetchone()['option_file']
    filepath = os.path.join(FILE_STORAGE, option_file)
    with open(filepath, 'r') as fp:
        option = json.load(fp)
    # 读取对比列，进行数据对比
    compare = jsonpath.jsonpath(option, '$.compares')

    # 查询table
    with VarietySheetDB() as v_cursor:
        v_cursor.execute(f'SELECT * FROM {table_name} WHERE id=1;')
        columns = v_cursor.fetchone()
        del columns['id']
        del columns['column_0']
    if compare:
        compare = compare[0]
    else:
        compare = {}
    # print(compare)
    return {'message': '查询成功!', 'columns': columns, 'compares': compare}


@chart_router.post('/chart/{chart_id}/compare/', summary='设置对比解读')
async def post_chart_compares(chart_id: int = Path(..., ge=1), compare_data: dict = Body(...)):
    # 通过chart_id找到配置文件，设置对比解读字段，保存
    with MySqlZ() as cursor:
        cursor.execute('SELECT id,option_file FROM industry_user_chart WHERE id=%s;', (chart_id,))
        option_file = cursor.fetchone()['option_file']
    filepath = os.path.join(FILE_STORAGE, option_file)
    with open(filepath, 'r') as fp:
        option = json.load(fp)
    series_columns = jsonpath.jsonpath(option, '$.series_data..column_index')  # 取被拿来画线的数据列
    if not series_columns:
        return {'message': '图形未选择列，无法设置!'}
    # 不再设置的图形中的列，不能做对比
    to_delete = []
    for key in compare_data.keys():
        if key not in series_columns:
            to_delete.append(key)
    for k in to_delete:
        del compare_data[k]
    # 取出旧的对比解读设置
    old_compare = option.get('compares', {})  # option['compares']
    # print('old_compare', old_compare)
    # 根据新的一起合并
    for col_key, value in compare_data.items():
        old_ = old_compare.get(col_key, None)
        if not old_:  # 旧设置中不存在
            old_compare[col_key] = value
        else:
            for ov in old_:
                if ov not in value:
                    old_.remove(ov)
            old_compare[col_key] = list(set(old_ + value))
    # print('new_compare', old_compare)
    # 设置对比解读字段
    option['compares'] = old_compare  # 已经在old_compare中直接修改了
    # 保存文件
    with open(filepath, 'w', encoding='utf-8') as f:
        json.dump(option, f, indent=4)
    return {'message': '设置成功!'}


@chart_router.get("/chart/{chart_id}/", summary="获取图形的基本信息")
async def chart_base_info(chart_id: int):
    with MySqlZ() as cursor:
        cursor.execute(
            "SELECT title,variety_en,option_file,decipherment,suffix "
            "FROM industry_user_chart WHERE id=%s;", (chart_id,)
        )
        chart = cursor.fetchone()
    if chart:
        # 读取图形的配置信息
        option_filepath = os.path.join(FILE_STORAGE, chart["option_file"])
        with open(option_filepath, 'r', encoding='utf8') as fop:
            chart_options = json.load(fop)

        # 左轴配置
        y_axises = chart_options["y_axis"]
        x_axises = chart_options["x_axis"]
        left_axis = y_axises[0]
        # 左轴
        chart["left_axis"] = {
            "name": left_axis.get("name", ""), "min": left_axis.get("min", ''), "max": left_axis.get("max", '')
        }
        # 右轴
        if len(y_axises) > 1:
            right_axis = y_axises[1]
            chart["right_axis"] = {
                "name": right_axis.get("name", ""), "min": right_axis.get("min", ''), "max": right_axis.get("max", '')
            }
        # 起始时间
        chart["date_length"] = x_axises["date_length"]
        chart["start_year"] = chart_options["start_year"]
        chart["end_year"] = chart_options["end_year"]
        chart["watermark"] = chart_options["watermark"]
    return {"message": "查询成功!", "data": chart, 'xAxis': x_axises}


@chart_router.put("/chart/{chart_id}/", summary="修改图形的基本配置")
async def modify_chart_option(chart_id: int, option_item: ModifyChartOptionItem = Body(...)):
    with MySqlZ() as m_cursor:
        m_cursor.execute("SELECT id,option_file FROM industry_user_chart WHERE id=%s;", (chart_id,))
        chart_obj = m_cursor.fetchone()
        if not chart_obj:
            raise HTTPException(status_code=400, detail='Chart Not Found')
        option_filepath = os.path.join(FILE_STORAGE, chart_obj["option_file"])
        with open(option_filepath, 'r', encoding='utf8') as fp:
            option_json = json.load(fp)
        # 修改文件option
        y_axises = option_json['y_axis']
        x_axises = option_json['x_axis']
        # 左轴数据
        left_y = y_axises[0]
        left_y['name'] = option_item.left_name
        if option_item.left_min:
            left_y['min'] = float(option_item.left_min)
        if option_item.left_max:
            left_y['max'] = float(option_item.left_max)
            if left_y['max'] == 0:
                del left_y['max']
        if len(y_axises) > 1:  # 有右轴
            right_y = y_axises[1]
            right_y['name'] = option_item.right_name
            if option_item.right_min:
                right_y['min'] = float(option_item.right_min)
            if option_item.right_max:
                right_y['max'] = float(option_item.right_max)
                if right_y['max'] == 0:
                    del right_y['max']
        x_axises['date_length'] = option_item.date_length
        x_axises['showMaxLabel'] = option_item.show_max_label
        option_json['start_year'] = option_item.start_year if option_item.start_year else '0'
        option_json['end_year'] = option_item.end_year if option_item.end_year else '0'
        option_json['watermark'] = option_item.watermark
        # 写入数据库(修改解说)
        m_cursor.execute(
            "UPDATE industry_user_chart SET decipherment=%s WHERE id=%s;", (option_item.decipherment, chart_id)
        )
        # 修改配置
        with open(option_filepath, 'w', encoding='utf-8') as fp:
            json.dump(option_json, fp, indent=4)
    return {"message": "修改成功!"}


# 功能在.put("/chart/{chart_id}/")也有,本API同时保留
@chart_router.put("/chart-decipherment/{chart_id}/", summary="修改图形的解读信息")
async def chart_decipherment(
        chart_id: int,
        decipherment: str = Body(..., embed=True)
):
    with MySqlZ() as cursor:
        cursor.execute(
            "UPDATE industry_user_chart SET decipherment=%s WHERE id=%s;", (decipherment, chart_id)
        )
    return {"message": "修改成功!"}


@chart_router.put("/chart/{chart_id}/display/", summary="修改图形在主页或品种页显示或仅自己可见")
async def chart_display(
        chart_id: int,
        person: dict = Depends(logged_require),
        is_principal: int = Query(None, ge=0, le=2),
        is_petit: int = Query(None, ge=0, le=1),
        is_private: int = Query(None, ge=0, le=1)
):
    user_id = person['uid']
    with MySqlZ() as cursor:
        if is_principal is not None and is_petit is None and is_private is None:
            # 管理员设置为is_principal=2则可直接开启
            cursor.execute('SELECT id,role FROM user_user WHERE id=%s;', (user_id, ))
            user_role = cursor.fetchone()['role']
            if user_role in ['superuser', 'operator']:
                is_principal = 2
            update_sql = "UPDATE industry_user_chart SET is_principal=%s WHERE id=%s;"
            cursor.execute(update_sql, (str(is_principal), chart_id))
        elif is_petit is not None and is_principal is None and is_private is None:
            update_sql = "UPDATE industry_user_chart SET is_petit=%s WHERE id=%s;"
            cursor.execute(update_sql, (is_petit, chart_id))
        elif is_private is not None and is_principal is None and is_petit is None:
            update_sql = "UPDATE industry_user_chart SET is_private=%s WHERE id=%s;"
            cursor.execute(update_sql, (is_private, chart_id))
        else:
            pass
    return {"message": "设置成功!"}


@chart_router.delete("/chart/{chart_id}/", summary="用户删除图形")
async def delete_chart(chart_id: int = Path(..., ge=1), person: dict = Depends(logged_require)):
    user_id = person['uid']
    # 查询图形信息
    with MySqlZ() as cursor:
        cursor.execute("SELECT id,creator,option_file FROM industry_user_chart WHERE id=%s;", (chart_id, ))
        chart_info = cursor.fetchone()
        if not chart_info:
            raise HTTPException(status_code=400, detail="Unknown Chart")
        if chart_info["creator"] != user_id:
            # 查询操作用户信息
            cursor.execute("SELECT id,role FROM user_user WHERE id=%s;", (user_id, ))
            operator = cursor.fetchone()
            if not operator or operator["role"] not in ["superuser", "operator"]:
                raise HTTPException(status_code=403, detail="不能删除归属于他人的图形!")
        # 删除图形和配置
        option_file = os.path.join(FILE_STORAGE, chart_info["option_file"])
        if os.path.exists(option_file):
            os.remove(option_file)
        cursor.execute("DELETE FROM industry_user_chart WHERE id=%s;", (chart_id, ))
        return {"message": "删除成功!"}


